package org.ehe.business.bom.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.ehe.business.bom.domain.BomStructureTemp;
import org.ehe.business.bom.service.BomExcelImportService;
import org.ehe.business.bom.service.BomStructureTempService;
import org.ehe.common.core.utils.StringUtils;
import org.ehe.common.satoken.utils.LoginHelper;
import org.ehe.erp.domain.product.ErpProduct;
import org.ehe.erp.domain.product.ErpProductCategory;
import org.ehe.erp.domain.product.ErpMaterial;
import org.ehe.erp.domain.product.ErpProductUnit;
import org.ehe.erp.service.product.IErpProductCategoryService;
import org.ehe.erp.service.product.IErpProductService;
import org.ehe.erp.service.product.IErpProductUnitService;
import org.ehe.erp.service.product.MaterialService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import cn.idev.excel.EasyExcel;
import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author :zhangnn
 * @className :BomExcelImportServiceImpl
 * @description: BOM Excel导入服务实现（处理下拉选择框）
 * @date 2025-09-03 14:03:46
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class BomExcelImportServiceImpl implements BomExcelImportService {

    @Autowired
    private BomStructureTempService bomStructureTempService;

    @Autowired
    private IErpProductService erpProductService;

    @Autowired
    private IErpProductCategoryService erpProductCategoryService;

    @Autowired
    private MaterialService erpMaterialService;

    @Autowired
    private IErpProductUnitService erpProductUnitService;

    /**
     * 导入Excel文件到BOM结构
     */
    @Override
    @Transactional
    public String importExcelToBom(MultipartFile file, Long importId) {
        int totalRows = 0;
        int successRows = 0;
        int failureRows = 0;
        List<String> errorMessages = new ArrayList<>();

        try {
            // 预先加载字典数据
            Map<String, String> categoryMap = loadProductCategoryMap();
            Map<String, String> materialMap = loadMaterialMap();
            Map<String, Long> unitMap = loadUnitMap();

            // 使用Apache POI直接读取Excel（更好地处理下拉选择框）
            List<Map<String, Object>> excelData = readExcelWithPOI(file);
            totalRows = excelData.size();

            log.info("读取到{}行数据，开始处理...", totalRows);
            log.info("可用分类：{}", categoryMap.keySet());
            log.info("可用材料：{}", materialMap.keySet());
            log.info("可用单位：{}", unitMap.keySet());

            // 用于缓存产品信息
            Map<String, ErpProduct> productCache = new HashMap<>();
            List<ErpProduct> newProducts = new ArrayList<>();
            List<BomStructureTemp> bomStructures = new ArrayList<>();

            // 处理每行数据
            for (int i = 0; i < excelData.size(); i++) {
                Map<String, Object> row = excelData.get(i);
                try {
                    if (processRowData(row, importId, i + 1, productCache, newProducts,
                        bomStructures, categoryMap, materialMap, unitMap)) {
                        successRows++;
                    } else {
                        failureRows++;
                        errorMessages.add("第" + (i + 2) + "行数据处理失败");
                    }
                } catch (Exception e) {
                    failureRows++;
                    errorMessages.add("第" + (i + 2) + "行数据处理异常：" + e.getMessage());
                    log.error("处理第{}行数据时出错", i + 2, e);
                }
            }

            // 批量保存新产品
            if (!newProducts.isEmpty()) {
                erpProductService.saveBatch(newProducts);
                log.info("批量保存产品完成，数量：{}", newProducts.size());

                // 更新BOM结构中的产品ID
                updateBomStructureItemIds(bomStructures, productCache, newProducts);
            }

            // 批量保存BOM结构
            if (!bomStructures.isEmpty()) {
                bomStructureTempService.saveBatch(bomStructures);
                log.info("批量保存BOM结构完成，数量：{}", bomStructures.size());
            }

            String result = String.format("导入完成：总计 %d 行，成功 %d 行，失败 %d 行",
                totalRows, successRows, failureRows);

            if (!errorMessages.isEmpty()) {
                result += "\n错误详情：\n" + String.join("\n", errorMessages.subList(0, Math.min(10, errorMessages.size())));
            }

            return result;

        } catch (Exception e) {
            log.error("导入Excel失败", e);
            throw new RuntimeException("导入失败：" + e.getMessage());
        }
    }

    /**
     * 使用Apache POI读取Excel（更好地处理下拉选择框）
     */
    private List<Map<String, Object>> readExcelWithPOI(MultipartFile file) throws IOException {
        List<Map<String, Object>> result = new ArrayList<>();

        try {
            EasyExcel.read(file.getInputStream())
                .sheet(0)
                .registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
                    private Map<Integer, String> headers = new HashMap<>();
                    private int dataRowIndex = 0;

                    @Override
                    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                        headers.clear();
                        // 清理表头，移除特殊字符
                        for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                            if (entry.getValue() != null) {
                                String cleanHeader = entry.getValue().trim()
                                    .replace("*", "")  // 移除星号
                                    .replace("：", "")  // 移除中文冒号
                                    .replace(":", "");  // 移除英文冒号
                                headers.put(entry.getKey(), cleanHeader);
                            }
                        }
                        log.info("清理后的表头：{}", headers);
                    }

                    @Override
                    public void invoke(Map<Integer, String> data, AnalysisContext context) {
                        dataRowIndex++;

                        // 跳过说明行
                        if (dataRowIndex == 1) {
                            return;
                        }

                        Map<String, Object> rowData = new HashMap<>();
                        boolean hasValidData = false;

                        for (Map.Entry<Integer, String> entry : data.entrySet()) {
                            String headerName = headers.get(entry.getKey());
                            if (headerName != null && StringUtils.isNotBlank(entry.getValue())) {
                                String cleanValue = cleanString(entry.getValue());
                                rowData.put(headerName, cleanValue);
                                hasValidData = true;

                                // 调试关键字段
                                if (headerName.contains("名称") || headerName.contains("分类") ||
                                    headerName.contains("材料") || headerName.contains("单位")) {
                                    log.debug("第{}行 {}：[{}]", dataRowIndex, headerName, cleanValue);
                                }
                            }
                        }

                        if (hasValidData) {
                            log.debug("第{}行有效数据：{}", dataRowIndex, rowData);
                            result.add(rowData);
                        }
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {
                        log.info("Excel读取完成，有效数据行数：{}", result.size());
                    }
                })
                .doRead();

        } catch (Exception e) {
            log.error("读取Excel失败", e);
            throw new IOException("读取Excel失败: " + e.getMessage());
        }

        return result;
    }

    /**
     * 加载产品分类映射（名称 -> full_code）
     */
    private Map<String, String> loadProductCategoryMap() {
        Map<String, String> categoryMap = new HashMap<>();

        List<ErpProductCategory> categories = erpProductCategoryService.list(
            new LambdaQueryWrapper<ErpProductCategory>()
                .eq(ErpProductCategory::getDelFlag, 0)
                .eq(ErpProductCategory::getStatus, 0)
        );

        for (ErpProductCategory category : categories) {
            String cleanName = cleanString(category.getName());
            if (StringUtils.isNotBlank(cleanName) && StringUtils.isNotBlank(category.getFullCode())) {
                categoryMap.put(cleanName, category.getFullCode());
            }
        }

        log.info("加载产品分类{}个", categoryMap.size());
        return categoryMap;
    }

    /**
     * 加载材料映射（材料名称 -> material_code）
     */
    private Map<String, String> loadMaterialMap() {
        Map<String, String> materialMap = new HashMap<>();

        List<ErpMaterial> materials = erpMaterialService.list(
            new LambdaQueryWrapper<ErpMaterial>()
                .eq(ErpMaterial::getDelFlag, 0)
                .eq(ErpMaterial::getStatus, 1)
        );

        for (ErpMaterial material : materials) {
            String cleanName = cleanString(material.getMaterialName());
            if (StringUtils.isNotBlank(cleanName) && StringUtils.isNotBlank(material.getMaterialCode())) {
                materialMap.put(cleanName, material.getMaterialCode());
            }
        }

        log.info("加载材料类型{}个", materialMap.size());
        return materialMap;
    }

    /**
     * 加载单位映射（单位名称 -> id）
     */
    private Map<String, Long> loadUnitMap() {
        Map<String, Long> unitMap = new HashMap<>();

        List<ErpProductUnit> units = erpProductUnitService.list(
            new LambdaQueryWrapper<ErpProductUnit>()
                .eq(ErpProductUnit::getDelFlag, 0)
                .eq(ErpProductUnit::getStatus, 0)
        );

        for (ErpProductUnit unit : units) {
            String cleanName = cleanString(unit.getName());
            if (StringUtils.isNotBlank(cleanName) && unit.getId() != null) {
                unitMap.put(cleanName, unit.getId());
            }
        }

        log.info("加载计量单位{}个", unitMap.size());
        return unitMap;
    }


    /**
     * 处理单行数据 (已修改以支持层级分类)
     */
    private boolean processRowData(Map<String, Object> row, Long importId, int rowNumber,
                                   Map<String, ErpProduct> productCache, List<ErpProduct> newProducts,
                                   List<BomStructureTemp> bomStructures, Map<String, String> categoryMap,
                                   Map<String, String> materialMap, Map<String, Long> unitMap) {

        try {
            // --- MODIFIED START: 读取层级分类 ---
            String level1Cat = getStringValue(row, "种类");
            String level2Cat = getStringValue(row, "大类");
            String level3Cat = getStringValue(row, "次分类");
            String level4Cat = getStringValue(row, "细类");
            String level5Cat = getStringValue(row, "最后类");

            // 校验核心分类是否填写
            if (StringUtils.isBlank(level1Cat) || StringUtils.isBlank(level2Cat) || StringUtils.isBlank(level3Cat)) {
                log.warn("第{}行：种类、大类、次分类为必填项，已跳过。", rowNumber);
                return false;
            }

            // 校验所有存在的层级分类是否在下拉数据中
            List<String> categoryLevels = Arrays.asList(
                level1Cat, level2Cat, level3Cat, level4Cat, level5Cat
            );
            List<String> levelNames = Arrays.asList(
                "种类", "大类", "次分类", "细类", "最后类"
            );

            for (int i = 0; i < categoryLevels.size(); i++) {
                String levelValue = categoryLevels.get(i);
                String levelName = levelNames.get(i);

                // 只校验有值的层级
                if (StringUtils.isNotBlank(levelValue)) {
                    String cleanValue = cleanString(levelValue);
                    if (!categoryMap.containsKey(cleanValue)) {
                        String errorMsg = String.format(
                            "第%s行：%s「%s」不存在于下拉选项中，请检查",
                            rowNumber, levelName, levelValue
                        );
                        log.error(errorMsg);
                        // 直接抛出异常中断处理，会被上层捕获并计入错误
                        throw new IllegalArgumentException(errorMsg);
                    }
                }
            }

            // 从后往前，确定最末级的有效分类名称
            String finalCategoryName = level5Cat;
            if (StringUtils.isBlank(finalCategoryName)) {
                finalCategoryName = level4Cat;
            }
            if (StringUtils.isBlank(finalCategoryName)) {
                finalCategoryName = level3Cat;
            }

            // 使用最末级分类名称去匹配 full_code
            String categoryFullCode = categoryMap.get(cleanString(finalCategoryName));
            if (categoryFullCode == null) {
                // 优化错误日志，提供完整的分类路径，方便调试
                log.error("第{}行：找不到最末级分类[{}]的匹配项。请检查分类名称是否正确。用户填写的路径：{} -> {} -> {} -> {} -> {}",
                    rowNumber, finalCategoryName, level1Cat, level2Cat, level3Cat, level4Cat, level5Cat);
                return false;
            }

            // 获取Excel其他列数据
            String productName = getStringValue(row, "产品名称");
            String productCode = getStringValue(row, "产品编码"); // 假设模板中可能没有产品编码列
            String materialType = getStringValue(row, "材料"); // 模板中是 "材料*"
            String specification = getStringValue(row, "规格"); // 模板中是 "规格*"
            String unit = getStringValue(row, "单位"); // 模板中是 "单位*"
            String quantityStr = getStringValue(row, "数量"); // 假设有数量列
            String remark = getStringValue(row, "备注");

            log.debug("第{}行原始数据 - 名称:[{}], 最末级分类:[{}], 材料:[{}], 单位:[{}]",
                rowNumber, productName, finalCategoryName, materialType, unit);

            // 验证必填字段
            if (StringUtils.isBlank(productName)) {
                log.warn("第{}行：产品名称为空，已跳过。", rowNumber);
                return false;
            }

            // --- REMOVED START: 删除旧的单一分类处理逻辑 ---
            // String productCategory = getStringValue(row, "产品分类");
            // if (StringUtils.isNotBlank(productCategory)) { ... }
            // --- REMOVED END ---

            // 处理材料
            String materialCode = null;
            if (StringUtils.isNotBlank(materialType)) {
                materialCode = materialMap.get(cleanString(materialType));
                if (materialCode == null) {
                    log.warn("第{}行：找不到材料类型[{}]，将忽略。可选：{}", rowNumber, materialType, materialMap.keySet());
                }
            }

            // 处理单位
            Long unitId = null; // 先设为null，如果没有匹配到则在创建产品时处理
            if (StringUtils.isNotBlank(unit)) {
                Long foundUnitId = unitMap.get(cleanString(unit));
                if (foundUnitId != null) {
                    unitId = foundUnitId;
                } else {
                    log.warn("第{}行：找不到计量单位[{}]，将使用默认或置空。可选：{}", rowNumber, unit, unitMap.keySet());
                }
            }
            if (unitId == null) {
                // 如果单位未找到或未填写，可以设置一个默认值，例如ID为1的单位
                unitId = 1L;
                log.warn("第{}行：单位未匹配或为空，已设置为默认单位(ID=1)", rowNumber);
            }


            // 处理数量
            BigDecimal quantity = parseQuantity(quantityStr);

            // 创建产品唯一标识
            String productKey = createProductKey(productCode, specification, productName);

            // 获取或创建产品
            ErpProduct product = getOrCreateProduct(productKey, productName, productCode,
                categoryFullCode, materialCode, specification, remark, unitId, productCache, newProducts);

            System.out.println("创建创建的产品信息"+product);
            // 创建BOM结构
            BomStructureTemp bomStructure = createBomStructure(importId, product,
                product != null ? product.getCategoryCode() : productCode,
                productName, specification, unit, quantity, rowNumber, remark);
            bomStructures.add(bomStructure);

            return true;
        } catch (Exception e) {
            log.error("处理第{}行数据失败：{}", rowNumber, e.getMessage(), e);
            return false;
        }
    }



    /**
     * 创建产品唯一标识
     */
    private String createProductKey(String code, String specification, String name) {
        StringBuilder key = new StringBuilder();
        if (StringUtils.isNotBlank(code)) {
            key.append(cleanString(code));
        }
        key.append("|");
        if (StringUtils.isNotBlank(specification)) {
            key.append(cleanString(specification));
        }
        key.append("|");
        if (StringUtils.isNotBlank(name)) {
            key.append(cleanString(name));
        }
        return key.toString();
    }

    /**
     * 获取或创建产品
     */
    private ErpProduct getOrCreateProduct(String productKey, String name, String code,
                                          String categoryFullCode, String materialCode, String specification,
                                          String remark, Long unitId, Map<String, ErpProduct> productCache,
                                          List<ErpProduct> newProducts) {

        // 检查缓存
        ErpProduct cachedProduct = productCache.get(productKey);
        if (cachedProduct != null) {
            return cachedProduct;
        }

        // 构建查询条件
        LambdaQueryWrapper<ErpProduct> queryWrapper = new LambdaQueryWrapper<ErpProduct>()
            .eq(ErpProduct::getDelFlag, 0);

        // 根据编码查询（如果有编码）
        if (StringUtils.isNotBlank(code)) {
            queryWrapper.and(wrapper -> wrapper
                .eq(ErpProduct::getBarCode, code)
                .or()
                .eq(ErpProduct::getCustomizeCode, code)
            );
        } else {
            // 没有编码时，根据名称+规格查询
            queryWrapper.eq(ErpProduct::getName, name);
            if (StringUtils.isNotBlank(specification)) {
                queryWrapper.eq(ErpProduct::getStandard, specification);
            }
        }

        ErpProduct existProduct = erpProductService.getOne(queryWrapper);

        if (existProduct != null) {
            productCache.put(productKey, existProduct);
            return existProduct;
        }

        // 检查待保存列表中是否已存在
        for (ErpProduct p : newProducts) {
            String existKey = createProductKey(p.getBarCode(), p.getStandard(), p.getName());
            if (productKey.equals(existKey)) {
                productCache.put(productKey, p);
                return p;
            }
        }

        // 创建新产品
        ErpProduct newProduct = createNewProduct(name, code, categoryFullCode, materialCode,
            specification, remark, unitId);

        if (newProduct != null) {
            newProducts.add(newProduct);
            productCache.put(productKey, newProduct);
        }

        return newProduct;
    }

    /**
     * 创建新产品
     */
    private ErpProduct createNewProduct(String name, String code, String categoryFullCode,
                                        String materialCode, String specification, String remark, Long unitId) {

        ErpProduct product = new ErpProduct();
        product.setName(name);

        // 处理产品编码
        if (StringUtils.isNotBlank(specification)) {
            product.setBarCode("");
            product.setCustomizeCode(specification);
        } else {
            String generatedCode = generateProductCode(name, specification);
            product.setBarCode(generatedCode);
            product.setCustomizeCode(generatedCode);
        }

        product.setCategoryCode(categoryFullCode);
        if(StringUtils.isBlank(code)){
            String[] split = categoryFullCode.split(".");
            code = Arrays.asList(split).get(0);
        }
        product.setType(code);
        product.setUnitId(unitId);
        product.setStatus(0L); // 启用状态
        product.setStandard(specification);
        product.setMaterial(materialCode); // 使用材料编码
        product.setRemark(remark);
        product.setDelFlag(0L);
        product.setTenantId(LoginHelper.getTenantId());

        return product;
    }

    /**
     * 生成产品编码
     */
    private String generateProductCode(String name, String specification) {
        String base = StringUtils.isNotBlank(specification) ? specification : name;
        return "AUTO_" + Math.abs(base.hashCode()) % 100000 + "_" + System.currentTimeMillis() % 10000;
    }


    /**
     * 创建BOM结构
     */
    private BomStructureTemp createBomStructure(Long importId, ErpProduct product, String categoryCode,
                                                String itemName, String specification, String unit,
                                                BigDecimal quantity, int seqNo, String remark) {

        //System.out.println("创建BOM结构==="+product);

        BomStructureTemp structure = new BomStructureTemp();
        structure.setImportId(importId);
        structure.setParentItemId(null);
        Long productId = product.getId();
        structure.setItemId(null != productId?productId:null); //后续赋值
        if (StringUtils.isNotBlank(product.getMaterial())) {
            structure.setItemCode(categoryCode+"."+product.getMaterial()+"."+specification);
        } else {
            structure.setItemCode(categoryCode+"."+specification);
        }
        structure.setItemName(itemName);
        structure.setItemType(determineItemType(product));
        structure.setSpecification(specification);
        structure.setUnit(null != product? String.valueOf(product.getUnitId()) : unit);
        structure.setQuantity(quantity != null ? quantity : BigDecimal.ONE);
        structure.setScrapRate(BigDecimal.ZERO);
        structure.setLevelNo(1);
        structure.setSeqNo(seqNo);
        structure.setIsKeyItem(0);
        structure.setIsOptional(0);
        structure.setRemark(remark);
        structure.setDelFlag("0");
        structure.setTenantId(LoginHelper.getTenantId());

        return structure;
    }

    /**
     * 确定物料类型
     */
    private Integer determineItemType(ErpProduct product) {
        if (product == null || StringUtils.isBlank(product.getType())) {
            return 1; // 默认原材料
        }

        String type = product.getType().toLowerCase();
        switch (type) {
            case "原材料": return 1;
            case "半成品": return 2;
            case "成品": return 3;
            case "标准": return 4;
            case "外购": return 5;
            default: return 1;
        }
    }

    /**
     * 更新BOM结构中的产品ID
     */
    private void updateBomStructureItemIds(List<BomStructureTemp> bomStructures,
                                           Map<String, ErpProduct> productCache,
                                           List<ErpProduct> newProducts) {
        for (ErpProduct product : newProducts) {
            if (product.getId() != null) {
                String key = createProductKey(product.getCategoryCode()+"."
                    +product.getMaterial()+"."
                    +product.getStandard(), product.getStandard(), product.getName());
                productCache.put(key, product);
            }
        }

        for (BomStructureTemp structure : bomStructures) {
            if (structure.getItemId() == null) {
                String key = createProductKey(structure.getItemCode(), structure.getSpecification(), structure.getItemName());
                ErpProduct product = productCache.get(key);
                if (product != null && product.getId() != null) {
                    structure.setItemId(product.getId());
                }
            }
        }
    }

    /**
     * 解析数量
     */
    private BigDecimal parseQuantity(String quantityStr) {
        if (StringUtils.isBlank(quantityStr)) {
            return BigDecimal.ONE;
        }

        try {
            return new BigDecimal(cleanString(quantityStr));
        } catch (NumberFormatException e) {
            log.warn("无法解析数量：{}", quantityStr);
            return BigDecimal.ONE;
        }
    }

    /**
     * 获取字符串值
     */
    private String getStringValue(Map<String, Object> row, String key) {
        Object value = row.get(key);
        if (value == null) {
            return null;
        }
        return cleanString(value.toString());
    }

    /**
     * 清理字符串
     */
    private String cleanString(String str) {
        if (str == null) {
            return null;
        }
        str = str.trim().replaceAll("\\s+", " ");
        return str.isEmpty() ? null : str;
    }
}
